package Reports;

import java.awt.Color;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Stack;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import poi.RandomDemo;

public class Reports {
	private XSSFWorkbook wb = null;
	private File xlsx = new File("C:/重复邮箱.xlsx");

	public Reports() {
		if (wb == null) {
			wb = new XSSFWorkbook();
		}
	}

	public static void main(String[] args) throws IOException {
		Reports w = new Reports();
		w.CreateSheet();
	}

	public void CreateSheet() throws IOException {

		int count = 0;
		String company = "";
		String code = "";
		String name = "";
		String email = "";

		int before = 0;
		int after = 0;

		XSSFSheet sheet = wb.createSheet("test");
		wb.setSheetName(0, "重复的邮箱");
		XSSFRow row = sheet.createRow(0);
		sheet.createFreezePane(0, 1);
		cteateCell(wb, row, 0, "邮箱");
		cteateCell(wb, row, 1, "所在部门");
		cteateCell(wb, row, 2, "部门代码");
		cteateCell(wb, row, 3, "姓名");

		int temp1 = "邮箱".getBytes().length;
		int temp2 = "所在部门".getBytes().length;
		int temp3 = "部门代码".getBytes().length;
		int temp4 = "姓名".getBytes().length;

		Query q = new Query();
		Map<String, List> map = q.QueryInfo();

		Iterator it1 = null;
		Map.Entry entry = null;
		it1 = map.entrySet().iterator();
		while (it1.hasNext()) {
			entry = (Map.Entry) it1.next();
			email = (String) entry.getKey();
			ArrayList list = (ArrayList) entry.getValue();
			int len = list.size();
			before = count + 1;
			for (int i = 0; i < len; i++) {
				count++;
				XSSFRow row2 = sheet.createRow(count);// 生成行
				String[] arr = (String[]) list.get(i);
				company = arr[0];
				code = arr[1];
				name = arr[2];

				cteateCell(wb, row2, 0, email);
				cteateCell(wb, row2, 1, company);
				cteateCell(wb, row2, 2, code);
				cteateCell(wb, row2, 3, name);

				temp2 = Math.max(temp2, company.getBytes().length);
				temp3 = Math.max(temp3, code.getBytes().length);
				temp4 = Math.max(temp4, name.getBytes().length);
			}
			after = count;

			temp1 = Math.max(temp1, email.getBytes().length);
			CellRangeAddress index = new CellRangeAddress(before, after, 0, 0);// 合并单元格
			sheet.addMergedRegion(index);
		}

		sheet.setColumnWidth(0, Math.min(temp1 + 1, 255) * 256);
		sheet.setColumnWidth(1, Math.min(temp2 + 1, 255) * 256);
		sheet.setColumnWidth(2, Math.min(temp3 + 1, 255) * 256);
		sheet.setColumnWidth(3, Math.min(temp4 + 1, 255) * 256);

		FileOutputStream fileOut = new FileOutputStream(xlsx);
		wb.write(fileOut);
		fileOut.close();
		String cmd = "cmd /c start C:/重复邮箱.xlsx";
		Runtime.getRuntime().exec(cmd);
	}

	private void cteateCell(XSSFWorkbook wb2, XSSFRow row, int column,
			String value) {
		XSSFCell cell = row.createCell(column);
		cell.setCellValue(value);
		XSSFCellStyle cellstyle = wb.createCellStyle();
		cellstyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
		cell.setCellStyle(cellstyle);
	}
}
